package forum.dao.mysql;

import forum.dao.TopicDAO;
import forum.dao.util.MySQLUtil;
import forum.entities.Topic;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class MySQLTopicDAO implements TopicDAO {
    private static final String CREATE_QUERY =
            "INSERT INTO topics(name,parentCategoryId,authorId) VALUES(NULL,NULL,NULL)";
    private static final String FIND_TOPIC_BY_ID_QUERY =
            "SELECT * FROM topics WHERE id = ?";
    private static final String UPDATE_QUERY =
            "UPDATE topics SET name = ?, parentCategoryId = ?, authorId = ? WHERE id = ?";
    private static final String DELETE_QUERY =
            "DELETE FROM topics WHERE id = ?";
    private static final String FIND_TOPICS_BY_CATEGORY_ID_QUERY =
            "SELECT * FROM topics WHERE parentCategoryId = ?";

    private static ConnectionPool connectionPool = null;

    // TODO Migrate driver initialising and connection establishment to db in DAOFactory.
    static {
        String createTableQuery = "CREATE TABLE IF NOT EXISTS topics" +
                "(id BIGINT(20) PRIMARY KEY NOT NULL AUTO_INCREMENT, " +
                "name VARCHAR(50), " +
                "parentCategoryId BIGINT(20), " +
                "authorId BIGINT(20))";
        Connection conn = null;
        PreparedStatement createTableStatement = null;
        try {
            connectionPool = C3P0ConnectionPool.getInstance();
            conn =  connectionPool.getConnection();
            createTableStatement = conn.prepareStatement(createTableQuery);
            createTableStatement.execute();
        } catch (SQLException e) {
            // TODO auto-generated try-catch block
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        } finally {
            MySQLUtil.close(createTableStatement);
            MySQLUtil.close(conn);
        }
    }

    @Override
    public Topic create() {
        Topic topic = null;
        Connection connection = null;
        PreparedStatement updateTopicStatement = null;
        ResultSet generatedKey = null;
        try {
            connection = connectionPool.getConnection();
            updateTopicStatement = MySQLUtil.prepareStatement(connection, CREATE_QUERY, true);
            updateTopicStatement.executeUpdate();
            generatedKey = updateTopicStatement.getGeneratedKeys();
            if (!generatedKey.next()) {
                // TODO Create DAOException class and add DAOException to GenericDAO methods signature.
                // throw new DAOException("No generated key obtained");
            }
            topic = new Topic(BigInteger.valueOf(generatedKey.getLong(1)));

        } catch (SQLException e) {
            // TODO auto-generated try-catch block
            e.printStackTrace();
        } finally {
            MySQLUtil.close(generatedKey);
            MySQLUtil.close(updateTopicStatement);
            MySQLUtil.close(connection);
        }
        return topic;
    }

    @Override
    public Topic read(BigInteger id) {
        Topic topic = null;
        Connection connection = null;
        PreparedStatement findTopicByIdStatement = null;
        ResultSet rs = null;
        try {
            connection = connectionPool.getConnection();
            findTopicByIdStatement = MySQLUtil.prepareStatement(connection, FIND_TOPIC_BY_ID_QUERY, false, id);
            rs = findTopicByIdStatement.executeQuery();
            if (!rs.next()) {
                return topic;
            }
            topic = new Topic(BigInteger.valueOf(rs.getLong("id")),
                    rs.getString("name"),BigInteger.valueOf(rs.getLong("parentCategoryId")),
                    BigInteger.valueOf(rs.getLong("authorId")));
        } catch (SQLException e) {
            // TODO auto-generated try-catch block
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        } finally {
            MySQLUtil.close(rs);
            MySQLUtil.close(findTopicByIdStatement);
            MySQLUtil.close(connection);
        }
        return topic;
    }

    @Override
    public void update(Topic topic) {
        if (topic.getId() == null) {
            throw new IllegalArgumentException("Empty id field");
        }
        Object [] fields = {
                topic.getTopicName(),
                topic.getParentCategoryId(),
                topic.getAuthorId(),
                topic.getId()
        };
        Connection connection = null;
        PreparedStatement createTopicStatement = null;
        try {
            connection = connectionPool.getConnection();
            createTopicStatement = MySQLUtil.prepareStatement(connection, UPDATE_QUERY, false, fields);
            createTopicStatement.executeUpdate();
        } catch (SQLException e) {
            // TODO auto-generated try-catch block
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        } finally {
            MySQLUtil.close(createTopicStatement);
            MySQLUtil.close(connection);
        }
    }

    @Override
    public void delete(BigInteger id) {
        Connection connection = null;
        PreparedStatement deleteTopicStatement = null;
        try {
            connection = connectionPool.getConnection();
            deleteTopicStatement = MySQLUtil.prepareStatement(connection, DELETE_QUERY, false, id);
            deleteTopicStatement.executeUpdate();
        } catch (SQLException e) {
            // TODO auto-generated try-catch block
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        } finally {
            MySQLUtil.close(deleteTopicStatement);
            MySQLUtil.close(connection);
        }
    }

    @Override
    public List<Topic> findTopicsByParentCategoryId(BigInteger parentCategoryId) {
        List<Topic> foundTopics = new ArrayList<Topic>();
        Connection connection = null;
        PreparedStatement findTopicsByCategoryIdStatement = null;
        ResultSet resultSet = null;
        try {
            connection = connectionPool.getConnection();
            findTopicsByCategoryIdStatement = MySQLUtil.prepareStatement(connection,
                    FIND_TOPICS_BY_CATEGORY_ID_QUERY,false,parentCategoryId);
            resultSet = findTopicsByCategoryIdStatement.executeQuery();
            while (resultSet.next()) {
                foundTopics.add(new Topic(BigInteger.valueOf(resultSet.getLong("id")),
                        resultSet.getString("name"),
                        BigInteger.valueOf(resultSet.getLong("parentCategoryId")),
                        BigInteger.valueOf(resultSet.getLong("authorId"))));
            }
        } catch (SQLException e) {
            // TODO auto-generated try-catch block
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        } finally {
            MySQLUtil.close(resultSet);
            MySQLUtil.close(findTopicsByCategoryIdStatement);
            MySQLUtil.close(connection);
        }
        return foundTopics;
    }
}
